﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlServerCe;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using ORM_Micro.Contracts;
using ORM_Micro.Helpers;

namespace ORM_Micro.SQLServerCE
{
    public class DataContext : IDataContext
    {
        #region Constants

        private const string dotReplacer = "___";

        #endregion

        #region Fields

        private readonly SqlCeConnection _connection;
        private SqlCeTransaction _transaction;
        private ExpandoObject _dataContextBag;

        #endregion

        #region Properties

        /// <summary>
        /// Dynamic property, allowing the use of multiple named parameters
        /// </summary>
        public dynamic DataContextBag {
            get { return _dataContextBag ?? (_dataContextBag = new ExpandoObject()); }
        }

        #endregion

        #region Constructor

        /// <summary>
        /// DataContext constructor, allowing a provider-adaptated connectionstring
        /// </summary>
        /// <param name="connectionString">provider-adaptated connectionstring</param>
        public DataContext(string connectionString)
        {
            _connection = new SqlCeConnection(connectionString);
            _connection.Open();
        }

        #endregion

        #region IDisposable Method

        public void Dispose()
        {
            if (_connection == null)
                return;
            //On rollback les transactions encore ouverte
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction.Dispose();
                _transaction = null;
            }
            //On clot et supprime la connection
            _connection.Close();
            _connection.Dispose();
        }

        #endregion

        #region IDataContext CRUD Methods

        public void CreateEntity(string createStatement, out object entityKey)
        {
            var command = new SqlCeCommand(createStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            command.ExecuteNonQuery();

            HandleReturnedParameters(command);

            //On renvoit la clé qui vient d'être créée, fonctionne sauf pour GUID
            command.CommandText = "select @@IDENTITY";
            entityKey = command.ExecuteScalar();
        }

        public TReturn ReadEntity<TReturn>(string readStatement) where TReturn : new()
        {
            var command = new SqlCeCommand(readStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var reader = command.ExecuteReader(CommandBehavior.SingleRow | CommandBehavior.SingleResult);

            HandleReturnedParameters(command);

            return FillEntity<TReturn>(reader);
        }

        public IEnumerable<TReturn> ReadEntities<TReturn>(string readStatement) where TReturn : new()
        {
            var command = new SqlCeCommand(readStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var reader = command.ExecuteReader(CommandBehavior.SingleResult);

            HandleReturnedParameters(command);

            return FillEntities<TReturn>(reader);
        }

        public void UpdateEntity(string updateStatement)
        {
            var command = new SqlCeCommand(updateStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            command.ExecuteNonQuery();

            HandleReturnedParameters(command);
        }

        public void DeleteEntity(string deleteStatement)
        {
            var command = new SqlCeCommand(deleteStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            command.ExecuteNonQuery();

            HandleReturnedParameters(command);
        }

        #endregion

        #region IDataContext Methods

        public TReturn GetValue<TReturn>(string getStatement)
        {
            var command = new SqlCeCommand(getStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var value = command.ExecuteScalar();

            HandleReturnedParameters(command);

            return (value == null || value == DBNull.Value) ? default(TReturn) : (TReturn)typeof(TReturn).ConvertFrom(value);
        }

        public IEnumerable<TReturn> GetValues<TReturn>(string getStatement)
        {
            var command = new SqlCeCommand(getStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var reader = command.ExecuteReader(CommandBehavior.SingleResult);
            var values = new List<TReturn>();

            while (reader.Read())
            {
                values.Add(reader.IsDBNull(0) ? default(TReturn) : (TReturn)typeof(TReturn).ConvertFrom(reader[0]));
            }

            HandleReturnedParameters(command);

            return values;
        }

        public void Call(string statement)
        {
            var command = new SqlCeCommand(statement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            command.ExecuteNonQuery();

            HandleReturnedParameters(command);
        }

        public IEnumerable<dynamic> Resolve(string resolveStatement)
        {
            var command = new SqlCeCommand(resolveStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var reader = command.ExecuteReader(CommandBehavior.SingleResult);

            HandleReturnedParameters(command);

            return FillResolvedEntities(reader);
        }

        public IEnumerable<IEnumerable<dynamic>> ResolveMany(string resolveStatement)
        {
            var command = new SqlCeCommand(resolveStatement, _connection) { Transaction = this._transaction };

            InjectParameters(command);

            var reader = command.ExecuteReader();

            HandleReturnedParameters(command);

            while (reader.HasRows)
            {
                yield return FillResolvedEntities(reader);
                reader.NextResult();
            }
        }

        #endregion

        #region IDataContext Transaction

        public void BeginTransaction()
        {
            if (_transaction == null)
                _transaction = _connection.BeginTransaction();
        }

        public void RollbackTransaction()
        {
            if (_transaction != null)
            {
                _transaction.Rollback();
                _transaction.Dispose();
                _transaction = null;
            }
        }

        public void CommitTransaction()
        {
            if (_transaction != null)
            {
                _transaction.Commit();
                _transaction.Dispose();
                _transaction = null;
            }
        }

        #endregion

        #region Internal Methods

        internal T FillEntity<T>(SqlCeDataReader reader) where T : new()
        {
            if (reader.Read())
            {
                var properties = new Dictionary<int, PropertyInfo>();
                for (var i = 0; i < reader.FieldCount; i++)
                {
                    var property = typeof(T).GetProperty(reader.GetName(i));
                    if (property != null)
                        properties.Add(i, property);
                }

                var entity = new T();

                for (var i = 0; i < reader.FieldCount; i++)
                {
                    //On n'affecte la valeur que si la propriété fait parti de l'objet final
                    if (properties.ContainsKey(i))
                        properties[i].SetValue(entity, reader.IsDBNull(i) ? properties[i].PropertyType.GetDefaultValue() : properties[i].PropertyType.ConvertFrom(reader[i]), null);
                }

                return entity;
            }

            return default(T);
        }

        internal IEnumerable<T> FillEntities<T>(SqlCeDataReader reader) where T : new()
        {
            var properties = new Dictionary<int, PropertyInfo>();
            for (var i = 0; i < reader.FieldCount; i++)
            {
                var property = typeof(T).GetProperty(reader.GetName(i));
                if (property != null)
                    properties.Add(i, property);
            }

            while (reader.Read())
            {
                var entity = new T();

                for (var i = 0; i < reader.FieldCount; i++)
                {
                    //On n'affecte la valeur que si la propriété fait parti de l'objet final
                    if (properties.ContainsKey(i))
                        properties[i].SetValue(entity, reader.IsDBNull(i) ? properties[i].PropertyType.GetDefaultValue() : properties[i].PropertyType.ConvertFrom(reader[i]), null);
                }

                yield return entity;
            }
        }

        internal IEnumerable<dynamic> FillResolvedEntities(SqlCeDataReader reader)
        {
            while (reader.Read())
            {
                dynamic resolvedEntity = new ExpandoObject();
                var values = (IDictionary<String, Object>)resolvedEntity;

                for (var i = 0; i < reader.FieldCount; i++)
                {
                    values.Add(reader.GetName(i), reader.IsDBNull(i) ? reader[i].GetType().GetDefaultValue() : reader[i]);
                }

                yield return resolvedEntity;
            }
        }

        internal void HandleReturnedParameters(SqlCeCommand command)
        {
            var parameterValues = (IDictionary<String, Object>)DataContextBag;
            //Loop on command parameters
            foreach (SqlCeParameter parameter in command.Parameters)
            {
                var parameterName = parameter.ParameterName.Replace("@", "").Replace(dotReplacer, ".");
                //Don't need to handle input parameters, they didn't change
                if (parameter.Direction == ParameterDirection.Input)
                    continue;
                //Add or modify parameters in DataContextBag
                if (parameterValues.ContainsKey(parameterName))
                    parameterValues[parameterName] = parameter.Value;
                else
                    parameterValues.Add(parameterName, parameter.Value);
            }
        }

        internal void InjectParameters(SqlCeCommand command)
        {
            //On définit le pattern de recherche de paramètres
            var parameterRegex = new Regex(@"@(\??[\w.]+)");
            //On associe les paramètres avec leur nom
            var parameterValues = (IDictionary<String, Object>)DataContextBag;
            //On supprime les entrées correspondantes à des propriétés d'objet
            parameterValues = (IDictionary<String, Object>)parameterValues.Where(v => !v.Key.Any(k => k == '.'));
            //On crée un dictionnaire pour stocker les afférences nom/valeur
            var parameterDictionary = parameterRegex.Matches(command.CommandText).Cast<Match>().Select(m => m.Groups[1].Value).Distinct().ToDictionary(m => m, m => m.Split(new[] { "?", "." }, StringSplitOptions.RemoveEmptyEntries));
            //On boucle sur les paramètres retrouvés dans la commande
            foreach (var parameter in parameterDictionary)
            {
                //On initialise les variables de travail
                var path = "";
                object value = null;
                //On boucle sur les différentes parties du paramètre
                foreach (var parameterPart in parameter.Value)
                {
                    path += parameterPart;
                    //On calcule la valeur si on ne l'a pas encore fait
                    if (!parameterValues.ContainsKey(path))
                    {
                        //Si la valeur est nulle à cet endroit, alors le paramètre trouvé dans la commande n'a pas de correspondance avec les objets fournis en paramètre
                        if (value == null)
                            continue;
                        //On calcule la valeur et on la stocke
                        var propertyInfo = value.GetType().GetProperty(parameterPart);
                        parameterValues.Add(path, propertyInfo.GetValue(value, null));
                    }
                    //On utilise la valeur stockée précédemment
                    value = parameterValues[path];
                    //On rajoute un point pour reproduire le nom du paramètre complet, lorsque l'on rajoutera le nom de la propriété suivante
                    path += ".";
                }
                //Si la valeur est nulle, on passe au paramètre suivant
                if (value == null)
                    continue;
                //On remplace le paramètre par la valeur correspondante
                var key = "@" + parameter.Key.Replace("?", "").Replace(".", dotReplacer);
                command.CommandText = command.CommandText.Replace("@" + parameter.Key, key);
                command.Parameters.AddWithValue(key, value);
                //Si le paramètre commence par un point d'interrogation, alors c'est un paramètre input/output
                if (parameter.Key.StartsWith("?"))
                    command.Parameters[key].Direction = ParameterDirection.InputOutput;
            }
        }

        #endregion
    }
}
